<?php

Yii::import('application.models._base.BasePelunasanUtangDetil');
class PelunasanUtangDetil extends BasePelunasanUtangDetil
{
    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }
    public static function get_utang($supplier_id)
    {
        $commnand = Yii::app()->db->createCommand("
        SELECT @ROW := @ROW + 1 AS row,spp.* FROM (
SELECT pp.pembelian_id id,0 trans_mode,pp.tgl,pp.no_faktur_beli no_faktur,pp.total nilai,
pp.sisa_tagihan - (IF (SUM(ppud.kas_dibayar) IS NULL,0,SUM(ppud.kas_dibayar))) AS sisa
FROM psn_pembelian AS pp
LEFT  JOIN psn_pelunasan_utang_detil AS ppud ON  ppud.trans_mode = 0 AND pp.pembelian_id = ppud.mode_id
INNER JOIN psn_terima AS pt ON  pt.terima_id = pp.ref_id
INNER JOIN psn_po AS ppo ON pt.po_id = ppo.po_id
WHERE pp.total >= 0 AND NOT pp.lunas AND ppo.supplier_id = :supplier_id
GROUP BY pp.pembelian_id,pp.no_faktur_beli,pp.tgl,pp.total
UNION
SELECT pp.pembelian_id id,0 trans_mode,pp.tgl,pp.no_faktur_beli no_faktur,pp.total nilai,
pp.sisa_tagihan - (IF(SUM(ppud.kas_dibayar) IS NULL,0,SUM(ppud.kas_dibayar))) sisa
FROM	psn_pembelian AS pp
LEFT JOIN psn_pelunasan_utang_detil AS ppud ON ppud.trans_mode = 0 AND pp.pembelian_id = ppud.mode_id
WHERE  pp.ref_id = :supplier_id AND NOT pp.lunas AND pp.total < 0
GROUP BY	pp.pembelian_id,pp.no_faktur_beli,pp.tgl,pp.total
UNION
SELECT pnd.nota_debet_id id,1 trans_mode,pnd.tgl,pnd.doc_ref no_faktur,pnd.total nilai,
pnd.total - (IF (SUM(ppud.kas_dibayar) IS NULL,0,SUM(ppud.kas_dibayar))) AS sisa
FROM psn_nota_debet AS pnd
LEFT JOIN psn_pelunasan_utang_detil AS ppud ON ppud.trans_mode = 1 AND pnd.nota_debet_id = ppud.mode_id
WHERE  NOT pnd.lunas AND pnd.supplier_id = :supplier_id
GROUP BY pnd.nota_debet_id,pnd.doc_ref,pnd.tgl,pnd.total
) spp,(SELECT @ROW := 0) r
WHERE spp.sisa != 0");
        return $commnand->queryAll(true, array(':supplier_id' => $supplier_id));
    }
    public static function get_detil($pelunasan_utang_id)
    {
        $comm = Yii::app()->db->createCommand("
        SELECT COALESCE(pp.tgl,pnd.tgl) tgl,COALESCE(pp.no_faktur_beli,pnd.doc_ref) no_faktur,
        COALESCE(pp.sub_total,pnd.total) nilai,ppud.sisa, ppud.kas_dibayar
        FROM
        psn_pelunasan_utang_detil AS ppud
        LEFT JOIN psn_pembelian AS pp ON ppud.trans_mode = 0 AND ppud.mode_id = pp.pembelian_id
        LEFT JOIN psn_nota_debet AS pnd ON ppud.trans_mode = 1 AND ppud.mode_id = pnd.nota_debet_id
        WHERE ppud.pelunasan_utang_id = :pelunasan_utang_id");
        return $comm->queryAll(true, array(':pelunasan_utang_id' => $pelunasan_utang_id));
    }
}